﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace BLL
{
    public class HOSOBENHANBLL
    {
        DataService DB = new DataService();

        //get all values
        public List<HOSOBENHAN> getALL()
        {
            string sql = "select * from hosobenhan";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<HOSOBENHAN> lstHs = new List<HOSOBENHAN>();
            foreach (DataRow r in table.Rows)
            {
                HOSOBENHAN hsba = new HOSOBENHAN();
                hsba.mabenhan = (int)r[0];
                hsba.mabaiviet = (int)r[1];
                try{hsba.ketluan = (string)r[2];}catch { hsba.ketluan = "";}
                try { hsba.file_patch = (string)r[3]; }
                catch { hsba.file_patch = ""; }

                lstHs.Add(hsba);
            }
            return lstHs;
        }

        //insert value
        public void insert(int mabaiviet, string ketluan, string file_patch)
        {

            string sql = "insert into hosobenhan values (@mabaiviet,@ketluan,@file_patch)";

            SqlParameter paraMaBv = new SqlParameter("mabaiviet", mabaiviet);
            SqlParameter paraKl = new SqlParameter("ketluan", ketluan);
            SqlParameter paraFile = new SqlParameter("file_patch", file_patch);

            DB.ExecuteNonQuery(sql, paraMaBv, paraKl, paraFile);

        }

        public void update(int mabenhan, int mabaiviet, string ketluan,string file_patch)
        {
            string sql = "update hosobenhan set mabaiviet = '" + mabaiviet.ToString() + ", ketluan = '" + ketluan + "', file_patch = '" + file_patch + "' where mabenhan = " + mabenhan.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int mabenhan)
        {
            string sql = "delete from hosobenhan where mabenhan = " + mabenhan.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public HOSOBENHAN getBestNewHosobanhan() {
            string sql = "select top 1 * from hosobenhan order by mabenhan desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);
            DataRow r = table.Rows[0];

            HOSOBENHAN hsba = new HOSOBENHAN();
            hsba.mabenhan = (int)r[0];
            hsba.mabaiviet = (int)r[1];
            try { hsba.ketluan = (string)r[2]; }
            catch { hsba.ketluan = "";}
            try { hsba.file_patch = (string)r[3]; }
            catch { hsba.file_patch = "";}

            return hsba;
        }

        //get count
        public int count()
        {
            string sql = "select top 1 * from baiviet order by mabaiviet desc";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public void updateKetluanWithIDBaiviet(int idBaiviet, string ketluan) {
            string sql = "update hosobenhan set ketluan = N'" + ketluan + "' from hosobenhan hsba,baiviet bv where bv.mabaiviet = " + idBaiviet.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public HOSOBENHAN getHosobenhanWithIdBaiviet(int idBaiviet) {
            string sql = "select * from hosobenhan where mabaiviet = " + idBaiviet.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            HOSOBENHAN hsba = new HOSOBENHAN();
            DataRow r = table.Rows[0];

            hsba.mabenhan = Int32.Parse(r[0].ToString());
            hsba.mabaiviet = Int32.Parse(r[1].ToString());
            hsba.ketluan = r[2].ToString();
            try { hsba.file_patch = r[3].ToString(); }
            catch { hsba.file_patch = ""; }

            return hsba;
        }
    }
}
